--Trigger 1
--1.1
USE QLDatPhong

if exists (select * from sysobjects where name = 'trg_BatDau_KetThuc_GD' and type = 'TR')
begin
	drop trigger trg_BatDau_KetThuc_GD
end
go


create trigger trg_BatDau_KetThuc_GD on GIAO_DICH
for Insert, Update
as
	if exists (select * from Inserted where BAT_DAU >= KET_THUC)
	begin
		raiserror('Thoi gian bat dau hoac ket thuc cua giao dich khong phu hop', 16, 1)
		rollback transaction
	end
go

--1.2

if exists (select * from sysobjects where name = 'trg_BatDau_KetThuc_CTGD' and type = 'TR')
begin
	drop trigger trg_BatDau_KetThuc_CTGD
end
go

create trigger trg_BatDau_KetThuc_CTGD on CHI_TIET_GD
for Insert, Update
as
	if exists (select * from Inserted where BAT_DAU >= KET_THUC)
	begin
		raiserror('Thoi gian bat dau hoac ket thuc cua chi tiet giao dich khong phu hop', 16, 1)
		rollback transaction
	end
go

--Trigger 2
--2.1

if exists (select * from sysobjects where name = 'trg_BatDau_KetThuc_GD' and type = 'TR')
begin
	drop trigger trg_BatDau_KetThuc_GD
end
go

create trigger trg_ThoiGianThue_GD on GIAO_DICH
for Update
as
	if exists (
		select * 
		from Inserted I, CHI_TIET_GD CT
		where I.MA_DOAN = CT.MA_DOAN and 
			(I.BAT_DAU > CT.BAT_DAU or  I.KET_THUC < CT.KET_THUC))
	begin
		raiserror('Thoi gian thue cua giao dich khong phu hop', 16, 1)
		rollback transaction
	end
go

--2.2

if exists (select * from sysobjects where name = 'trg_ThoiGianThue_CTGD' and type = 'TR')
begin
	drop trigger trg_ThoiGianThue_CTGD
end
go

create trigger trg_ThoiGianThue_CTGD on CHI_TIET_GD
for Insert, Update
as
	if exists (
		select * 
		from Inserted I, GIAO_DICH GD
		where I.MA_DOAN = GD.MA_DOAN and 
			(I.BAT_DAU < GD.BAT_DAU or  I.KET_THUC > GD.KET_THUC))
	begin
		raiserror('Thoi gian thue cua chi tiet giao dich khong phu hop', 16, 1)
		rollback transaction
	end
go

--Trigger 3
--3.1

if exists (select * from sysobjects where name = 'trg_ThoiGianThue_GD_CTGD' and type = 'TR')
begin
	drop trigger trg_ThoiGianThue_GD_CTGD
end
go

create trigger trg_ThoiGianThue_GD_CTGD on CHI_TIET_GD
for Insert, Update
as
	if exists (
		select * from Inserted I, CHI_TIET_GD CTGD
		where I.Ma_Phong = CTGD.Ma_Phong
		and I.Ma_Doan <> CTGD.Ma_Doan
		and left(I.Ma_Phong, 2) = 'MU'
		and ((I.Bat_Dau <= CTGD.Ket_Thuc) and (I.Ket_Thuc >= CTGD.Bat_Dau)))
	begin
		raiserror('Hai chi tiet giao dich co cung phong, thoi gian trung lap va thuoc 2 doan khac nhau', 16 , 1)
		rollback transaction
	end
go

--Trigger 4
--4.1

if exists (select * from sysobjects where name = 'trg_SoGiuong_CTGD' and type = 'TR')
begin
	drop trigger trg_SoGiuong_CTGD
end
go

create trigger trg_SoGiuong_CTGD on CHI_TIET_GD
for Insert, Update
as
	if(	
		((select count(*) from Inserted I, CHI_TIET_GD CTGD
		where I.Ma_Phong = CTGD.Ma_Phong 
			and I.Bat_Dau <= CTGD.Ket_Thuc 
			and I.Ket_Thuc >= CTGD.Bat_Dau 
			and left(I.Ma_Phong, 2) = 'SI')) > 
		(select LP.So_Giuong from LOAI_PHONG LP
				where LP.Ma_Loai = (select P.Loai_Phong from Inserted I2,PHONG P
							where P.Ma_Phong = I2.Ma_Phong))
		or
		((select count(*) from Inserted I, CHI_TIET_GD CTGD
		where I.Ma_Phong = CTGD.Ma_Phong 
			and I.Bat_Dau <= CTGD.Ket_Thuc 
			and I.Ket_Thuc >= CTGD.Bat_Dau 
			and left(I.Ma_Phong, 2) = 'MU') > 2)
	    )
	begin
		raiserror('Vuot qua so luong cho mot Phong', 16, 1)
		rollback transaction
	end
go

--Trigger 5
--5.1

if exists (select * from sysobjects where name = 'trg_CMND_Phong_CTGD' and type = 'TR')
begin
	drop trigger trg_CMND_Phong_CTGD
end
go

create trigger trg_CMND_Phong_CTGD on CHI_TIET_GD
for update, insert
as
	if exists (select * from Inserted I, CHI_TIET_GD CTGD
			where (I.CMND = CTGD.CMND) 
				and (I.CMND <> null)
				and (I.Bat_Dau <= CTGD.Ket_Thuc)
				and (I.Ket_Thuc >= CTGD.Bat_Dau)
			)
	begin
		raiserror('Trung so chung minh nhan dan', 16 ,1)
		rollback transaction
end
go

--Trigger 6
--6.1

if exists (select * from sysobjects where name = 'trg_CapNhatSoNguoiSoPhong_CTGD' and type = 'TR')
begin
	drop trigger trg_CapNhatSoNguoiSoPhong_CTGD
end
go

create trigger trg_CapNhatSoNguoiSoPhong_CTGD on CHI_TIET_GD
for Insert, Update, Delete
as
  begin
	declare @SoPhongUpdate int, @Ma_Doan varchar(5)

	set @SoPhongUpdate = 0

	if exists(select * from Inserted)
	begin
		if not exists(select * from Inserted I,CHI_TIET_GD CTGD
				where I.MA_PHONG = CTGD.MA_PHONG 
					and I.MA_DOAN = CTGD.MA_DOAN
					and I.MA_CHI_TIET <> I.MA_CHI_TIET)
			set @SoPhongUpdate = @SoPhongUpdate + 1

		select @MA_DOAN = MA_DOAN 
		from Inserted I

		update GIAO_DICH
		set SO_NGUOI = SO_NGUOI + 1, SO_PHONG = SO_PHONG + @SoPhongUpdate
		where MA_DOAN = @MA_DOAN
	end

	set @SoPhongUpdate = 0
	if exists(select * from Deleted)
	begin
		if not exists(select * from Deleted D,CHI_TIET_GD CTGD
				where D.MA_PHONG = CTGD.MA_PHONG
					and D.MA_DOAN = CTGD.MA_DOAN)
			set @SoPhongUpdate = @SoPhongUpdate - 1
		
		select @MA_DOAN = MA_DOAN 
		from Inserted I

		update GIAO_DICH
		set SO_NGUOI = SO_NGUOI - 1, SO_PHONG = SO_PHONG + @SoPhongUpdate
		where MA_DOAN = @MA_DOAN
	end

	if @@error <> 0 
		begin
			raiserror ('Error - Insert Or Update CHI_TIET_GD!!!', 16, 1)
			rollback transaction
		end
  	end
go